#!/usr/bin/python
#coding: utf-8

"""
创建all_info数据表，用来存储应用的所有信息
"""
import sys
sys.path.append("..")

from Config.Config import Insert_Sql_AllInfo, Create_Soft_AllInfo, Select_Sql_Soft_Info, Select_Sql_Soft_Survey, conn, PROCEDURE2, Select_Sql_AllInfo

class CreateInfo(object):

    def __init__(self):
        self.conn = conn
        self.conn.select_db("appchina")
        self.cur = self.conn.cursor()
        self.urls = self.SelectAllInfo()

    def Create(self):
        try:
            self.cur.execute(Create_Soft_AllInfo)

            self.conn.commit()
            print u"创建数据库成功"
        except Exception as e:
            print e
            print u"创建失败，回滚"
            self.conn.rollback()

        try:
            self.cur.execute(PROCEDURE2)

            self.conn.commit()
            print u"创建存储过程成功！！！！"
        except Exception as e:
            print e
            print u"存储过程之前已经创建"
            self.conn.rollback()

    def SelectAllInfo(self):
        self.conn.select_db("appchina")
        try:
            self.cur.execute(Select_Sql_AllInfo)

            self.urls = self.cur.fetchall()
        except Exception as e:
            print u"读取all_info信息失败", e

    def SelectSoftInfo(self):
        self.cur.execute(Select_Sql_Soft_Info)
        self.soft_info = list(self.cur.fetchall())

        self.cur.execute(Select_Sql_Soft_Survey)
        self.soft_survey = list(self.cur.fetchall())

    def InsertAllInfo(self):
        num = 1
        for survey in self.soft_survey:
            for info in self.soft_info:
                if survey[1] in info:
                    if (survey[1], ) in self.urls:
                        print u"该语句已经写入成功"
                        break
                    try:
                        # 使用存储过程进行存储
                        self.cur.execute("call p2('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s');" % (info[1], info[2], info[3], info[4], info[5], info[6], info[7], info[8], info[9], survey[3], survey[4]))
                        # 使用insert语句进行存储
                        # self.cur.execute(Insert_Sql_AllInfo % (info[1], info[2], info[3], info[4], info[5], info[6], info[7], info[8], info[9], survey[3], survey[4]))
                        self.conn.commit()

                        self.soft_info.remove(info) # info用完就删除，减少时间开销
                        print u"插入第%d条成功" % num
                        num += 1
                    except Exception as e:
                        print u"插入出错"
                        print e
                        self.conn.rollback()
                    else:
                        break

    def GetAllInfo(self):
        return len(self.urls)

    def Close(self):
        self.cur.close()
        self.conn.close()

def CreateAllInfoMain():
    import time
    t1 = time.time()
    print u"开始时间为：", time.ctime(time.time())
    c = CreateInfo()
    c.Create()
    c.SelectAllInfo()
    c.SelectSoftInfo()
    c.InsertAllInfo()
    c.SelectAllInfo()
    print u"all_info表中共有%d条数据", c.GetAllInfo()
    # c.Close() # 此语句加上以后报 pymysql.err.InterfaceError: (0, '') 错误
    t2 = time.time()
    print u"结束时间为：", time.ctime(time.time())
    print u"程序总用时为：", t2 - t1

if __name__ == "__main__":
    CreateAllInfoMain()
